// parts CRUD

var mysql = require('./db');

// <editor-fold defaultstate="collapsed" desc="readcategorieswidget = function(req, res)">
exports.readcategorieswidget = function(req, res) {

    var search = '';
    if(req.query.search) {
        search = 'WHERE cat_name LIKE "%' + req.query.search + '%" ';
    }

    mysql.query(
/*rows[0]*/ ' SELECT SQL_CALC_FOUND_ROWS cat_id,cat_name ' +
            ' FROM exxact.part_cats ' + search +
            ' LIMIT ' + req.query.start + ',' + req.query.limit + ';' +
/*rows[1]*/ ' SELECT FOUND_ROWS() total;',
    function (err, rows) {
        if(err) {
            res.json({ success: false, crud: 'select categories widget', err: err });
        }
        else {
            res.json({ success: true, data: rows[0], total: eval(rows[1])[0].total });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="read = function(req, res)">
exports.read = function(req, res) {

    var search = '';
    if(req.query.search) {
        search = 'WHERE ep.mfr_pn LIKE "%' + req.query.search + '%"';// OR ep.description LIKE "%' + req.query.search + '%"';
    }
    var sort = JSON.parse(req.query.sort)[0];

    mysql.query(
/*rows[0]*/ ' SELECT SQL_CALC_FOUND_ROWS ep.id id,ep.cat cat,ep.mfr_pn mfr_pn,ep.description description,ep.cost cost,ep.ingramSKU ingramSKU,' +
            ' ec.cat_name cat_name' +
            ' FROM exxact.parts ep ' +
            ' LEFT JOIN exxact.part_cats ec ON(ep.cat=ec.cat_id) ' + search +
            ' GROUP BY ep.id ' +
            ' ORDER BY ' + sort.property + ' ' + sort.direction +
            ' LIMIT ' + req.query.start + ',' + req.query.limit + ';' +
/*rows[1]*/ ' SELECT FOUND_ROWS() total;',
    function (err, rows) {
        if(err) {
            res.json({ success: false, crud: 'select parts', err: err });
        }
        else {
            res.json({ success: true, data: rows[0], total: eval(rows[1])[0].total });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="add = function(req, res)">
exports.add = function(req, res) {

    var param = req.body;

    mysql.query(
        'INSERT INTO exxact.parts SET cat=?,mfr_pn=?,description=?,cost=?,ingramSKU=?',
    [
        param.cat,param.mfr_pn,param.description,param.cost,param.ingramSKU
    ],
    function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'insert part', err: err });
        }
        else {
            result.id = result.insertId;
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="update = function(req, res)">
exports.update = function(req, res) {

    var param = req.body;
    var id = param.id;
    // delete extra column
    delete param.cat_name;
    console.log('param=',param);

    mysql.query('UPDATE exxact.parts SET ? WHERE id=?', [param, id], function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'update parts', err: err });
        }
        else {
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="destroy = function(req, res)">
exports.destroy = function(req, res) {
    //console.log('------delete parts delete');
    var id = req.body.id;
    console.log('###DELETE FROM exxact.parts WHERE id=' + id);

    mysql.query('DELETE FROM exxact.parts WHERE id=?', [id], function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'delete parts', err: err });
        }
        else {
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="parsePartParams = function(body)">
parsePartParams = function(body) {

    var params = eval(body);

    var sql = [];

    if(params.cat)         { sql.push('cat='          + params.cat); }
    if(params.cat)         { sql.push('mfr_pn="'      + params.cat + '"'); }
    if(params.description) { sql.push('description="' + params.description + '"'); }
    if(params.cost)        { sql.push('cost='         + params.cost); }
    if(params.ingramSKU)   { sql.push('ingramSKU="'   + params.ingramSKU + '"'); }

    return { sql: sql.join(), id: params.id };
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="CREATE">
/*

CREATE TABLE exxact.parts (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cat` int(11) NOT NULL DEFAULT '0',
  `mfr_pn` varchar(32) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `cost` int(11) NOT NULL DEFAULT '0',
  `ingramSKU` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=461 DEFAULT CHARSET=latin1

// change table
ALTER TABLE parts ADD INDEX mfr_pn(mfr_pn);
ALTER TABLE parts MODIFY COLUMN description VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE parts ADD INDEX description(description(64));
ALTER TABLE parts MODIFY COLUMN ingramSKU VARCHAR(32) NOT NULL DEFAULT '';

// new table
CREATE TABLE exxact.parts (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cat` int(11) NOT NULL DEFAULT '0',
  `mfr_pn` varchar(32) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `cost` int(11) NOT NULL DEFAULT '0',
  `ingramSKU` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `mfr_pn` (`mfr_pn`),
  KEY `description` (`description`(64))
) ENGINE=MyISAM AUTO_INCREMENT=461 DEFAULT CHARSET=latin1

// change table part_cats
ALTER TABLE part_cats ADD INDEX cat_id(cat_id);
ALTER TABLE part_cats MODIFY COLUMN cat_name VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE part_cats MODIFY COLUMN cat_description VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE part_cats ADD INDEX cat_name(cat_name);

CREATE TABLE exxact.part_cats (
  `cat_id` int(11) NOT NULL DEFAULT '0',
  `cat_name` varchar(64) NOT NULL DEFAULT '',
  `cat_description` varchar(128) NOT NULL DEFAULT '',
  KEY `cat_id` (`cat_id`),
  KEY `cat_name` (`cat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Parts category table'

*/
// </editor-fold>
